from django.views.decorators.http import require_http_methods
from .confs.conf import get_mirrordb_conn
from django.http import HttpResponse, JsonResponse
from .pub import def_response_o
import base64, json

@require_http_methods(["GET"])
def usr_record_his(request):
    params = request.GET
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    re = json.loads(json.dumps(def_response_o))
    if not start or not end:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期"
        return JsonResponse(re)
    start = start.split(' ')[0]
    end = end.split(' ')[0]
    where = f"""
        DATE_SUB(`time`, INTERVAL 8 HOUR) >= '{start} 00:00:00.000000' 
        AND DATE_SUB(`time`, INTERVAL 8 HOUR) <= "{end} 23:59:59.000000" 
    """
    rets= []
    uid_rec = {}
    ori_uid_list = search_uid_records_by_date(start, end, where)
    ori_list = search_ori_records_by_where(where)

    for ori in ori_list:
        if ori['uid'] in uid_rec:
            uid_rec[ori['uid']]['list'].append(ori)
        else:
            uid_rec[ori['uid']] = {"list": [ori], "first":ori, "sec": ori}

        if uid_rec[ori['uid']]["first"]["id"]< ori["id"]:
            uid_rec[ori['uid']]["first"] = ori
        if uid_rec[ori['uid']]["sec"]["id"]>ori["id"] and uid_rec[ori['uid']]["first"]["id"]<ori["id"]:
            uid_rec[ori['uid']]["sec"] = ori

    l =0
    for u_r in uid_rec.items():
        rec = u_r[1]['first']
        rec["sec_url"] = u_r[1]["sec"]['url']
        rec["count"] = len(u_r[1]["list"])
        rets.append(rec)
        l+=1
        if l>100:
            break

    # print( rets )
    # mirror_db_conn = get_mirrordb_conn()
    # for ls in ori_uid_list:
    #     where_ext = where
    #     where_ext = f"{where_ext} AND uid={ls['uid']}"
    #     sql = f"""
    #     SELECT * FROM `gather_record` 
    #     WHERE 
    #     {where_ext}
    #     ORDER BY `id`
    #     LIMIT 2 OFFSET 1
    #     """
    #     sec_row = mirror_db_conn.query_one(sql)
    #     print(sec_row)
    #     rec = {
    #         "uid": ls['uid'],
    #         "first_id": ls["first_id"],
    #         "count": ls["count"],
    #         "first_url": ls["first_url"],
    #         "first_page": ls["first_url"].split("?")[0],
    #         "sec_id": sec_row['id'] if sec_row else '',
    #         "sec_url": sec_row['url'] if sec_row else '',
    #         "sec_page": sec_row['page'] if sec_row else ''
    #     }
    #     rets.append(rec)
    #     pass
    re['result'] = rets
    re['total'] = len(uid_rec.items())
    return JsonResponse(re,safe=False)

def search_uid_records_by_date(start, end, where):
    where_str = where or f"""
    DATE_SUB(`time`, INTERVAL 8 HOUR) >= '{start} 00:00:00.000000' 
    AND DATE_SUB(`time`, INTERVAL 8 HOUR) <= "{end} 23:59:59.000000" 
    """
    mirror_db_conn = get_mirrordb_conn()
    sql = f'''
    SELECT *, 
    (SELECT url FROM `gather_record` AS dd WHERE nt.first_id=dd.id) AS 'first_url'
    FROM 
    (SELECT
    uid, 
    MIN(id) AS first_id, 
    COUNT(*) AS `count` 
    FROM `gather_record` AS tt
    WHERE `host`="gentlebands.com" AND 
    {where_str}
    GROUP BY uid) AS nt
    '''

    results = mirror_db_conn.query_all(sql)
    return results

def search_ori_records_by_where(where):
    where_str = where
    mirror_db_conn = get_mirrordb_conn()
    sql = f'''
    SELECT
    *
    #(SELECT DISTINCT url FROM `gather_record` AS sd WHERE sd.uid=1006444021937 ORDER BY sd.id DESC LIMIT 1 OFFSET 1) AS second_highest_id
    FROM `gather_record` AS tt
    WHERE `host`="gentlebands.com" AND 
    {where_str}
    '''

    results = mirror_db_conn.query_all(sql)
    return results